package com.supermap.dataservice.cloud.mymine;

import org.apache.ibatis.annotations.*;
import org.apache.logging.log4j.util.Strings;

import java.util.List;
import java.util.Map;

/**
 * @author aqua
 * @description
 * @date 2019年12月12 17:09
 * @email 1301584033@qq.com
 */

@Mapper
public interface MymineMapper {

    @SelectProvider(type = MymineMapperSQL.class,method = "getFarmListSQL")
    List<Map<String,Object>> getFarmList(@Param("areaCode") String areaCode,@Param("userId")Integer userId,@Param("limit")Integer limit);

    @Select("select t1.userid,\n" +
            "case t1.roleid\n" +
            "when 3 then (select url from t_expertinfo t2 where t2.userid = t1.userid)\n" +
            "when 4 then (select url from t_company t2 where t2.userid = t1.userid)\n" +
            "when 6 then (select url from t_cooperative t2 where t2.userid = t1.userid)\n" +
            "when 7 then (select url from t_suply_point t2 where t2.userid = t1.userid)\n" +
            "when 10 then (select url from t_countryside t2 where t2.userid = t1.userid)\n" +
            "end url\n" +
            "from t_user_role t1\n" +
            "where t1.userid = #{userId}")
    List<Map<String,Object>> getUrl(@Param("userId") Integer userId);

    @Select("select * from t_user_score where userid=#{userId}")
    List<Map<String,Object>> getUserScore(@Param("userId") Integer userId);
    //菜单查询功能
    @SelectProvider(type = MymineMapperSQL.class,method = "getNewAreaRoleMenuSQL")
    List<Map<String,Object>> getNewAreaRoleMenu(@Param("userId") Integer userId,@Param("lens")Integer lens,@Param("code")String code);

    @Select("select s3.code,s3.sname as name,count(t2.id) num from t_user t1\n" +
            "inner join sys_area_menu_new s2 on left(t1.areaCode,2) = s2.areacode\n" +
            "inner join sys_menu_new s3 on s2.menucode = s3.code\n" +
            "inner join t_user_click_menu t2 on s3.code = t2.menuCode\n" +
            "where t1.id = #{userId} and s3.state=0 and s3.category = 2\n" +
            "group by s3.code\n" +
            "order by num desc\n" +
            "limit 5;")
    List<Map<String,Object>> getNewAreaRoleMenRedu(@Param("userId") Integer userId);
    //取消定制
    @Insert("insert ignore into sys_user_menu_cancel(userid,menucode) values(#{userId},#{menuCode})")
    Integer putNewMenu(@Param("userId") Integer userId,@Param("menuCode") String menuCode);

    //菜单定制
    @Delete("delete from sys_user_menu_cancel where userid=#{userId} and menucode=#{menuCode}")
    Integer delNewMenu(@Param("userId") Integer userId,@Param("menuCode") String menuCode);

    //获取作物
    @Select("select t1.code as id,t1.name,t1.img_ico as icon,\n" +
            "(select count(id) from t_user_crop where crop=t1.id and userid=#{userId}) as isorder\n" +
            " from (select * from t_crop  where `level`=1) t1")
    List<Map<String,Object>> getNewCorpList(@Param("userId") Integer userId);

    @Select("select t2.name,t2.code as id,t1.num from (select  t1.crop, count(t1.id) as num from t_user_crop t1 group by t1.crop order by num desc) t1\n" +
            "inner join t_crop t2\n" +
            "on t1.crop =t2.id limit 5")
    List<Map<String,Object>> getNewCorpReduList();

    //添加作物
    @Insert("insert ignore into t_user_crop(userid,crop) values(#{userId},#{cropId})")
    Integer putNewCrop(@Param("userId") Integer userId,@Param("cropId") Integer cropId);

    //取消作物
    @Delete("delete from t_user_crop where userid=#{userId} and crop=#{cropId}")
    Integer delNewCrop(@Param("userId") Integer userId,@Param("cropId") Integer cropId);
    class  MymineMapperSQL{
        public String getFarmListSQL(Map<String,Object> map){
            String areaCode=(String)map.get("areaCode");
            Integer userId=(Integer)map.get("userId");
            Integer limit=(Integer)map.get("limit");
            StringBuilder stringBuilder=new StringBuilder();
            stringBuilder.append("select t1.id as farmId,t1.farmname,t4.areaname,t3.name,t3.img_ico as cropimg,t1.longitude as lon,t1.latitude as lat, round(t1.`area`/666.66,2) as `area` from t_farmland t1\n" +
                    "            inner join t_farmcrops t2\n" +
                    "            on t1.id=t2.farmland\n" +
                    "            inner join t_crop t3\n" +
                    "            on t2.crop=t3.code\n" +
                    "            inner join t_area t4\n" +
                    "            on t1.code=t4.areacode\n" +
                    "            where  1=1 ");
            if(areaCode !=null && Strings.isNotBlank(areaCode)){
                stringBuilder.append(" and t4.areacode like concat('"+areaCode+"','%') ");
            }
            if(userId!=null){
                stringBuilder.append(" and t1.userId="+userId+" ");
            }
            stringBuilder.append(" order by t1.updatetime desc limit "+limit+" ");
            return  stringBuilder.toString();
        }
        public String getNewAreaRoleMenuSQL(Map<String,Object> map){
                String code=(String)map.get("code");
                Integer userId=(Integer)map.get("userId");
                Integer lens=(Integer)map.get("lens");
                StringBuilder stringBuilder=new StringBuilder();
                stringBuilder.append("select s3.code,s3.name,s3.sname,s3.url,s3.icon,s3.category,if(s4.id is null,1,0) isorder from t_user t1\n" +
                        "inner join t_user_role t2 on  t1.id = t2.userid\n" +
                        "inner join sys_area_role s1 on  left(t1.areaCode,2) = s1.areacode and t2.roleid = s1.roleid\n" +
                        "inner join sys_area_role_menu_new s2 on s1.id =s2.arearoleid\n" +
                        "inner join sys_menu_new s3 on s2.menucode = s3.code\n" +
                        " left join sys_user_menu_cancel s4 on s2.menucode = s4.menucode and t1.id=s4.userid " +
                        "where t1.id = "+userId+" and  s3.state=0 and length(s3.code)="+lens+" ");
                if(code!=null){
                    stringBuilder.append(" and s3.code like concat('"+code+"','%') ");
                }
                stringBuilder.append(" order by s3.order_num");

                return  stringBuilder.toString();
        }
    }
}
